package com.example.sugar_demo.mapper;

import com.example.sugar_demo.bean.Exam;
import com.example.sugar_demo.bean.Paper;
import com.example.sugar_demo.bean.KeywordCount;
import com.example.sugar_demo.bean.Province;
import org.apache.ibatis.annotations.Select;


import java.math.BigDecimal;
import java.util.List;

public interface TradeMapper {

    
    @Select("SELECT\n" +
        "    province_name,\n" +
        "    sum(order_count) AS order_count,\n" +
        "    sum(order_amount) AS order_amount\n" +
        "FROM dws_trade_province_order_window\n" +
        "WHERE toYYYYMMDD(stt) = #{date}\n" +
        "GROUP BY province_name")
    List<Province> province(int date);

    @Select(" select sum(register_ct) from dws_user_user_register_window " +
            " where toYYYYMMDD(stt) = #{date} " )
    BigDecimal getUserRegister(int date);

    @Select(" select keyword,sum(keyword_count) keyword_count from edu2023.dws_traffic_keyword_page_view_window " +
            " where toYYYYMMDD(stt) = #{date} " +
            " group by keyword ")
    List<KeywordCount> keywordCount(int date);

    @Select("SELECT\n" +
            "    course_name,\n" +
            "    sum(uv_ct) AS uv_ct,\n" +
            "    sum(score_ct) AS score_ct,\n" +
            "    sum(duration_sec_ct) AS duration_sec_ct\n" +
            "FROM dws_interaction_exam_count\n" +
            "WHERE toYYYYMMDD(stt) = #{date}\n" +
            "GROUP BY course_name\n" +
            "ORDER BY score_ct DESC\n" +
            "LIMIT #{limit}")
    List<Exam> exam(int date,int limit);

    @Select("SELECT\n" +
            "    paper_title,\n" +
            "    score,\n" +
            "    sum(uv_ct) AS uv_ct\n" +
            "FROM dws_interaction_exam_count\n" +
            "WHERE toYYYYMMDD(stt) = #{date}\n" +
            "GROUP BY paper_title , score \n" +
            "ORDER BY uv_ct DESC\n" +
            "LIMIT #{limit}")
    List<Paper> paper(int date,int limit);
}